﻿using System;
using System.Collections.Generic;
using System.Data;
using DocumentFormat.OpenXml.Packaging;
using System.Xml;
using System.Xml.Linq;
using System.Xml.XPath;
using System.IO;

namespace ExcelSax {
    public class ExcelSaxConnection : IDbConnection {
        #region Statics
        
        public static readonly String NsSpreadsheetml = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
        public static readonly String NsRelationShip = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";

        #endregion

        #region Privates

        private Boolean _isDisposed;
        private String _excelFileName;
        private FileStream _streamDoc;
        private SpreadsheetDocument _spreadsheetDocument;
        private ConnectionState _cs;

        // ======================================
        // gestion des la listes des feuilles de calcul
        private List<ExcelSax.Sheet> _sheetList;                

        // ======================================
        // gestion interne de SharedStringTablePart
        private NameTable _zxnt;
        private XmlNamespaceManager _nsmanager;
        private XElement _sharedStringTable;
        private List<String> _sharedStringTableAsList;        

        // ======================================
        // gestion des formats de cellules
        private List<DocumentFormat.OpenXml.Spreadsheet.CellFormat> _cellFormats;        

        #endregion

        #region Properties

        public SpreadsheetDocument Document { get { this.Open();  return _spreadsheetDocument; } private set { _spreadsheetDocument = value; } }
        public List<ExcelSax.Sheet> Sheets { get { this.Open();  return _sheetList; } }
        public List<String> SharedStringTableAsList { get { this.Open(); return _sharedStringTableAsList; } }
        public List<DocumentFormat.OpenXml.Spreadsheet.CellFormat> CellFormats { get { this.Open(); return _cellFormats; } }

        #endregion

        #region Birth
        //---------------------------------------------------------------------
        public ExcelSaxConnection (String connectionString) {
            // l'appel à Open dans les accesseurs des énumérations garantie qu'elle ne sont pas null sauf si le bindDocument échoue
            _isDisposed = false;            
            _spreadsheetDocument = null;
            _excelFileName = connectionString;
            _cs = ConnectionState.Closed;
        }        

        #endregion        

        #region IDbConnection Membres            
        //---------------------------------------------------------------------
        public void Close (Boolean force) {
            if (force || _cs != ConnectionState.Closed ) {
                if ( _sheetList != null )
                    _sheetList.Clear();
                if ( _sharedStringTable != null)
                    _sharedStringTableAsList.Clear();
                if ( _cellFormats != null )
                    _cellFormats.Clear();
                if (_spreadsheetDocument != null) {                    
                    _spreadsheetDocument.Close();
                    _spreadsheetDocument = null;                    
                }
                if ( _streamDoc != null ) {
                    _streamDoc.Close();
                    _streamDoc = null;
                }
                _cs = ConnectionState.Closed;
            }
        }

        public void Close() {
            Close(false);
        }
        //---------------------------------------------------------------------
        public string ConnectionString {
            get {
                return _excelFileName;
            }
            set {
                _excelFileName = value;
            }
        }
        //---------------------------------------------------------------------
        public string Database {
            get { return _excelFileName; }
        }
        //---------------------------------------------------------------------
        public void Open() {
            try {
                if (_cs == ConnectionState.Closed) {
                    _cs = ConnectionState.Connecting;
                    bindDocument();
                    initSheetList();
                    initSharedStringTable();
                    initCellFormats();
                    _cs = ConnectionState.Open;
                }
            } catch (Exception ex) {
                Close(true);
                throw ex;
            }
        }
        //---------------------------------------------------------------------
        public ConnectionState State {
            get { return _cs; }
        }

            #region Not Implemented

        public IDbTransaction BeginTransaction(IsolationLevel il) {
            throw new NotImplementedException();
        }

        public IDbTransaction BeginTransaction() {
            throw new NotImplementedException();
        }

        public void ChangeDatabase(string databaseName) {
            throw new NotImplementedException();
        }

        public int ConnectionTimeout {
            get { throw new NotImplementedException(); }
        }

        public IDbCommand CreateCommand() {
            throw new NotImplementedException();
        }

            #endregion                

        #endregion

        #region IDisposable Membres

        public void Dispose () {
            if ( !_isDisposed ) {
                this.Close();
                _isDisposed = true;
            }
        }

        #endregion

        #region Methods

            #region Privates
        //---------------------------------------------------------------------
        private void bindDocument() {
            if (Document != null) return;
            if (_excelFileName == null) throw new ArgumentNullException("_excelFileName");
            if (!System.IO.File.Exists(_excelFileName)) throw new System.IO.FileNotFoundException(_excelFileName);
            _streamDoc = File.Open(_excelFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            Document = SpreadsheetDocument.Open(_streamDoc, false);
        }
        //---------------------------------------------------------------------
        private void initSheetList() {
            if (_sheetList == null) _sheetList = new List<Sheet>(); else _sheetList.Clear();
            WorkbookPart workbookPart = Document.WorkbookPart;
            foreach (var v in workbookPart.Workbook.Sheets) {
                _sheetList.Add(new Sheet {
                    Name = v.GetAttribute("name", "").Value,
                    Id = v.GetAttribute("id", ExcelSaxConnection.NsRelationShip).Value
                });
            }
        }
        //---------------------------------------------------------------------
        private void initSharedStringTable() {
            if (_zxnt == null) _zxnt = new NameTable();
            if (_sharedStringTableAsList == null) _sharedStringTableAsList = new List<string>(); else _sharedStringTableAsList.Clear();
            if (_nsmanager == null) {
                _nsmanager = new XmlNamespaceManager(_zxnt);
                _nsmanager.AddNamespace("def", ExcelSaxConnection.NsSpreadsheetml);
            }
            _sharedStringTable = XElement.Load(XmlReader.Create(Document.WorkbookPart.SharedStringTablePart.GetStream()));
            loadListOfSharedStringFromXElement(_sharedStringTableAsList, _sharedStringTable);
        }
        //---------------------------------------------------------------------
        private void initCellFormats() {
            if (_cellFormats == null) _cellFormats = new List<DocumentFormat.OpenXml.Spreadsheet.CellFormat>(); else _cellFormats.Clear();
            var cxfs = Document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
            foreach (var cxf in cxfs) {
                _cellFormats.Add(
                    new DocumentFormat.OpenXml.Spreadsheet.CellFormat(cxf.OuterXml));
            }
        }        

            #endregion

            #region Publics
        //---------------------------------------------------------------------
        public ExcelSaxDataReader GetReaderForSheet(String sheetName) {
            return new ExcelSaxDataReader(this, sheetName);
        }

            #endregion
        //---------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="l"></param>
        /// <param name="x"></param>
        protected void loadListOfSharedStringFromXElement(List<String> l, XElement x) {
            foreach (XElement xe in x.XPathSelectElements("//def:t", _nsmanager)) {
                l.Add(xe.Value);
            }            
        }

        #endregion
    }
    // ========================================================================
    public class Sheet {
        public String Name { get; set; }
        public String Id { get; set; }
    }
}
